Testing Stored Procs with JUnit

On an early XP project, our application was mandated to use stored procedures being developed by another group. It seemed that every time we integrated our Java with their PLSQL code, we found serious bugs in the fundamental behavior of their stored procedures. We were were writing automated tests using JUnit for our code and we were sure that writing unit tests for the stored procedures would help clarify the interface contract and would improve the quality of their code but we couldn't force the other team to write unit tests. Nor had utPLSQL even been invented.

We decided to try writing unit tests for the stored procedures in the xUnit family member we were comfortable with: JUnit. Since we had to write JDBC code to access the stored procedures anyway, we defined JUnit tests for each stored procedure via the JDBC PreparedStatement classes that we had built. The tests exercised the basic behavior of the stored behaviors and a few of the more obvious failure cases. Whenever we received a new version of the stored procedures, we would run the JUnit tests before even trying to exercise them from our application code. Needless to say, many of the tests failed.

We sat down with the developers building the stored procedures and showed them our tests and how they were failing left, right and center. Needless to say, they were a bit embarrassed but they agreed that our tests were correct. They went off to fix the stored procedures and gave us a new version to test. This fared somewhat better but still had some failurs. Then a very important thing happened: they asked to have a copy of the tests we had written and for instruction on how to run them for themselves. Before long, they were writing their own PLSQL unit tests in JUnit!

Page generated at Wed Feb 09 16:39:03 +1100 2011